package com.qingcheng.dao;

import com.qingcheng.pojo.order.CategoryReport;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.Mapper;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

/**
 * @author 戴金华
 * @date 2019-10-29 15:56
 */
public interface CategoryReportMapper extends Mapper<CategoryReport> {

    @Select("SELECT\n" +
            "\toi.category_id1,\n" +
            "\toi.category_id2,\n" +
            "\toi.category_id3,\n" +
            "\tDATE_FORMAT( o.pay_time, '%Y-%m-%d' ) count_date,\n" +
            "\tSUM( oi.num ) num,\n" +
            "\tSUM( oi.pay_money ) money \n" +
            "FROM\n" +
            "\ttb_order o,\n" +
            "\ttb_order_item oi \n" +
            "WHERE\n" +
            "\to.id = oi.order_id \n" +
            "\tAND o.`is_delete` = '0' \n" +
            "\tAND o.pay_status = '1' \n" +
            "\tAND DATE_FORMAT( o.pay_time, '%Y-%m-%d' ) = #{date} \n" +
            "GROUP BY\n" +
            "\toi.category_id1,\n" +
            "\toi.category_id2,\n" +
            "\toi.category_id3,\n" +
            "\tDATE_FORMAT( o.pay_time, '%Y-%m-%d' )")
    public List<CategoryReport> findAll(@Param("date") LocalDate date);


    @Select("SELECT category_id1 categoryId1,NAME category1Name,count_date,num,money " +
            "FROM tb_category_report cr,v_category v " +
            "WHERE cr.category_id1=v.id AND " +
            "count_date>=#{date1} AND count_date<=#{date2}" +
            "GROUP BY categoryId1,category1Name")
    public List<Map> category1Count(@Param("date1") String date1, @Param("date2") String date2);
}
